﻿
DECLARE @DATE_NOW NVARCHAR(8) =(SELECT MAX(CONVERT(NVARCHAR(8), BUSINESS_DATE,112)) FROM  M74.BICDATA.DBO.BALANCE_LIVE)
PRINT (@DATE_NOW)
DECLARE @DATE_NOW_1 NVARCHAR(10) = convert(nvarchar(10),(select convert(date, @DATE_NOW)),120)
PRINT (@DATE_NOW_1)
DECLARE @STARTOFMONTH NVARCHAR (20)= CONVERT (NVARCHAR(8),(DATEADD(M,DATEDIFF(MONTH, 0, CONVERT(DATE,@DATE_NOW)),0)),112)
PRINT @STARTOFMONTH
DECLARE @STARTOFMONTH_1 NVARCHAR (20)= convert(nvarchar(10),(select convert(date, @STARTOFMONTH)),120)
PRINT @STARTOFMONTH_1
DECLARE @ENDMONTH_LAST_3 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),120)
PRINT @ENDMONTH_LAST_3
DECLARE @ENDMONTH_LAST_3_1 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),112)
PRINT @ENDMONTH_LAST_3_1
DECLARE @YEARMONTH_LAST_MONTH VARCHAR(6) = CONVERT(NVARCHAR(6),DATEADD(D, -1,@STARTOFMONTH),112)
PRINT @YEARMONTH_LAST_MONTH

exec('
select * into SME2017DAILY.DBO.lntb_disbursement_'+@DATE_NOW+' 
from M74.BICDATA.DBO.LNTB_DISBURSEMENT where SEGMENT =''SMES''

select * into  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+' 
from M74.BICDATA.DBO.CARD_LIVE where business_date  ='''+@DATE_NOW_1+'''
and (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'' or CARD_NUMBER LIKE ''522384%'') 

select * into  SME2017DAILY.DBO.BALANCE_LIVE_'+@DATE_NOW+' 
from M74.BICDATA.DBO.BALANCE_LIVE where business_date  ='''+@DATE_NOW_1+'''
and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (select branch_id from branch_off) or (SEGMENT is null and LEFT(sector,1)>1)))

select * into  SME2017DAILY.DBO.FINNONE_LIVE_'+@DATE_NOW+' 
from M74.BICDATA.DBO.FINNONE_LIVE where business_date  ='''+@DATE_NOW_1+''' 
and BI_SEGMENT in (''CORPORATE'') or BI_SEGMENT is null
')

print('--- 1 - Lấy các bảng đầu vào và update thông tin---
--- 1.1 --- Lấy bảng Khách hàng')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''VPB_CUSTOMER_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'

select * into SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'
from M74.BICDATA.DBO.CUSTOMER where SEGMENT not in (''KHCN'',''HH'')
')

print('--- 1.2 --- Lấy bảng KHHĐ (sửa tên bảng, nội dung)')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''KHHD_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.KHHD_'+@DATE_NOW+'

select * into SME2017DAILY.DBO.KHHD_'+@DATE_NOW+'
from server12.tranglt9.dbo.active_customer_daily_report
where report_date = ''2017-05-15'' and STATUS = ''ACTIVE''')

print('--- 1.3 --- Lấy bảng Dư nợ, Huy động Bình quân ---')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''balance'+@DATE_NOW+'_sme'')
        DROP TABLE SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme

select * into SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
from M74.SME.[dbo].[DLYTB_AVG_BAL] where BUSINESS_DATE = '''+@DATE_NOW_1+'''

alter table SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
add PRODUCT_DETAIL_2 NVARCHAR(255),
SECTOR nvarchar(40),
LOAI_THE nvarchar (40),
SOURCE_ID nvarchar(200),
ISSUE_DATE datetime ')

exec('
UPDATE a
SET a.PRODUCT_DETAIL_2 = ''6.3. Unsecured Overdraft''
FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme  AS A 
inner join (SELECT * FROM  SME2017DAILY.DBO.lntb_disbursement_'+@DATE_NOW+' 
				WHERE (OVERDRAFT_COLLATERAL = ''N'' OR OVERDRAFT_COLLATERAL IS NULL)) AS b 
on A.ACCTNO collate database_default = B.ACCTNO
WHERE  A.PRODUCT_NAME = ''6. Overdraft'' 

UPDATE a 
SET a.SECTOR = B.SECTOR
FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme  AS A
inner join SME2017DAILY.DBO.BALANCE_LIVE_'+@DATE_NOW+' AS b 
on A.ACCTNO collate database_default = B.ACCTNO

UPDATE a 
	SET a.ISSUE_DATE = B.VALUE_DATE
	from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A
	inner join SME2017DAILY.DBO.LNTB_MASTER B
	on A.ACCTNO collate database_default  = B.ACCTNO 

UPDATE a
	SET a.ISSUE_DATE = B.ISSUE_DATE
	from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A
	inner join SME2017DAILY.DBO.DPTB_MASTER B
	on A.ACCTNO collate database_default  = B.ACCTNO 

Update a
Set a.APP_REJ_DATE = B.APP_REJ_DATE
From SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A,  SME2017DAILY.DBO.FINNONE_LIVE_'+@DATE_NOW+' B 
where A.RRT_ID COLLATE DATABASE_DEFAULT  = B.APP_ID_C and B.business_date ='''+@DATE_NOW_1+''' and BI_APPSSTATUS = ''APPROVED''

Update a
Set a.APP_REJ_DATE = B.DIST_DATE 
FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme  AS A ,  SME2017DAILY.DBO.lntb_disbursement_'+@DATE_NOW+'  AS b 
WHERE A.ACCTNO collate database_default = B.ACCTNO
AND A.APP_REJ_DATE is null

select *
	, case when  limit_ref like ''%05%'' then ''CTSDB''
			when limit_ref like ''%06%'' then ''KTSDB'' end as loai_the_m16
into #loai_the 
from M16.[VPB_WHR2].[dbo].[CARD_ISSUE_T24] 
where limit_ref like ''%06%'' or limit_ref like ''%05%''

update a
set a.SOURCE_ID = B.SOURCE_ID 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A,  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+'  B
where A.ACCTNO = convert(nvarchar,B.ACNT_CONTRACT_ID,100) and B.BUSINESS_DATE = '''+@DATE_NOW_1+'''

update a
set a.LOAI_THE = b.loai_the_m16
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme a
inner join #loai_the b
on a.SOURCE_ID = b.CARD_ID
')

print('--- 1.4 --- Lấy bảng CIF chuyển BAD BANK trong tháng')
exec('
--- Nếu chuyển vào rồi chuyển ra vẫn bị tính thì chạy code như sau
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''BADBANK_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+'

SELECT distinct CUSTOMER_ID CIF, DATE_GBBB INTO SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+'
FROM M16.[VPB_G3B].[DBO].[TBL_G3B_T24CRD] 
			WHERE BUSINESS_DATE between '''+@STARTOFMONTH_1+''' and '''+@DATE_NOW_1+'''			
				AND BRANCH_CODE_GBBB = ''VN9990001''
				AND DATE_GBBB IS NOT NULL and DATE_GBBB between '''+@STARTOFMONTH_1+''' and '''+@DATE_NOW_1+'''
')

print('--- 1.5 --- Lấy bảng LD chuyển BAD BANK trong tháng')
exec('
IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''BALANCE_BADBANK_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'

select *, ''YES'' NOXAU into SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'
from M74.sme.dbo.incentive'+@DATE_NOW+'
where CIF in (select CIF from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' where CIF is not null) and NHOMNOMOI in (''3'',''4'',''5'')

insert into SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+' (ACCTNO, BAL_QD, CIF, DAO, NOXAU)
select ACNT_CONTRACT_ID, case when BALANCE >= 0 then 0 else -BALANCE end, CONTRACT_CIF, T24_DAO,''YES''
from  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+'  where business_date ='''+@DATE_NOW_1+'''
and CONTRACT_CIF in (select CIF from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' where CIF is not null)
')

exec(' alter table SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'
add DAO_CIF nvarchar (50) ')

exec('update SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+' 
SET DAO_CIF = B.DAO
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'  A , SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
	WHERE A.CIF collate database_default = B.RECID
')

print('--- 1.6 --- Lấy bảng CARD')
exec('

IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''CARD_'+@DATE_NOW+''')
        DROP TABLE SME2017DAILY.DBO.CARD_'+@DATE_NOW+'

select *  into SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
from  SME2017DAILY.DBO.CARD_LIVE_'+@DATE_NOW+' 
where  BUSINESS_DATE ='''+@DATE_NOW_1+''' AND (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'' or CARD_NUMBER LIKE ''522384%'')

update SME2017DAILY.DBO.CARD_'+@DATE_NOW+' set T24_DAO = B.NEW_DAO
from SME2017DAILY.DBO.CARD_'+@DATE_NOW+' A, SME2017.DBO.CHUYEN_DAO_THE_'+@YEARMONTH_LAST_MONTH+' B
where A.SOURCE_ID = B.SOURCE_ID
')


print('--- 1.7 --- Update lại bảng KPIs Target')
exec('

IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''KPI_TARGET_201705_CHANGE'')
        DROP TABLE SME2017DAILY.dbo.KPI_TARGET_201705_CHANGE

select * into SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE from SME2017.DBO.KPI_TARGET_'+@YEARMONTH_LAST_MONTH+'

update SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE
set May2017 =  b.kpiS_may2017
from SME2017DAILY.DBO.KPI_TARGET_201705_CHANGE A, SME2017.DBO.KPIs_201705 B
where A.DAo = B.DAO and A.CODE = B.CODE and B.Branch_code = A.Branch_code
')

print('--- 1.8 --- Update lại KH chuyển giao trong tháng')
exec('
---drop table #CHUYEN_DAOCIF_201705
select RECID, DAO as OLD_DAO into #CHUYEN_DAOCIF_201705 from SME2017.DBO.VPB_CUSTOMER_20170430

alter table #CHUYEN_DAOCIF_201705
add NEW_DAO nvarchar (50)

update #CHUYEN_DAOCIF_201705
set NEW_DAO = B.DAO 
from #CHUYEN_DAOCIF_201705 A, SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
where A.RECID COLLATE DATABASE_DEFAULT = B.RECID

---drop table #tang_KHHD 
select NEW_DAO, count(RECID) CIF into #tang_KHHD
from #CHUYEN_DAOCIF_201705
where OLD_DAO <> NEW_DAO
AND RECID in (select CIF from SME2017.DBO.KHHD_201704)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' where  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in   (select channel from channel_off) or channel is null)
	AND SEGMENT=''SMES'' )
group by NEW_DAO 

---drop table #giam_KHHD 
select OLD_DAO, count(RECID) CIF into #giam_KHHD
from #CHUYEN_DAOCIF_201705
where OLD_DAO <> NEW_DAO
and RECID in (select CIF from SME2017.DBO.KHHD_201704)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' where  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in  (select channel from channel_off) or channel is null)
	AND SEGMENT=''SMES'' )
group by OLD_DAO

update SME2017.DBO.KPIs_201705
set ban_giao_201705 = B.CIF
from SME2017.DBO.KPIs_201705 a, #giam_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.OLD_DAO and a.code = ''7''

update SME2017.DBO.KPIs_201705
set nhan_ban_giao_201705 = B.CIF
from SME2017.DBO.KPIs_201705 a, #tang_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.NEW_DAO and a.code = ''7''
')